/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package DAO;

import DTO.PhieuThuePhong_DTO;
import java.sql.ResultSet;
import java.util.ArrayList;

/**
 *
 * @author Lenh Ho Xung
 */
public class PhieuThuePhong_DAO {

    private static String sqlString;
    private static MSSQLConnect cn;
    private static ResultSet rs;

    public static ArrayList<PhieuThuePhong_DTO> layDSKhachHangDangThue() throws Exception {
        ArrayList<PhieuThuePhong_DTO> ds = new ArrayList();
        sqlString = "SELECT * FROM PHIEUTHUEPHONG WHERE Thue = 1";
        try {
            //thực hiện câu truy vấn
            cn = new MSSQLConnect();
            //thực hiện câu truy vấn
            rs = cn.excuteQuery(sqlString);
            while (rs.next()) {
                PhieuThuePhong_DTO p = new PhieuThuePhong_DTO();
                p.setMaKhach(rs.getString("MaKhach"));
                ds.add(p);
            }
        } catch (Exception e) {
            throw new Exception("Không thể lấy danh sách mã dịch vụ");
        } finally {
            cn.Close();
        }
        return ds;
    }
    public static String laymaMax() throws Exception {
        String maphieuMax = new String();
        sqlString = "SELECT TOP 1 MaPhieuThue FROM PhieuThuePhong ORDER BY MaPhieuThue DESC";
        try {
            cn = new MSSQLConnect();
            rs = cn.excuteQuery(sqlString);
            while (rs.next()) {
                maphieuMax = rs.getString("MaPhieuThue");
            }
        } catch (Exception e) {
            throw new Exception("Không thể lấy mã max");

        } finally {
            cn.Close();
        }
        return maphieuMax;
    }

    public static int Insert(PhieuThuePhong_DTO p) throws Exception {
        int n;
        sqlString = "INSERT INTO PhieuThuePhong (MaPhieuThue, MaKhach ,NgayThue, NgayTra, SoKhach, TriGia) VALUES('"
                + p.getMaphieuThue() + "','"
                + p.getMaKhach() + "','"
                + p.getNgayThue() + "','"
                + p.getNgayTra() + "','"
                + p.getSoKhach() + "','"
                + p.getTriGia() + "')";

        //Kết nối CSDL
        try {
            cn = new MSSQLConnect();
            n = cn.executeUpdate(sqlString);
        } catch (Exception e) {
            System.out.println(e.toString());
            throw new Exception("Không thể thêm vào cơ sơ dữ liệu");
        } finally {
            cn.Close();
        }
        return n;
    }
    
    public static Float layTriGiaTheoMaKhach(String maKhach) throws Exception{
         Float trigia;
         trigia= 0f;
        sqlString = "SELECT TriGia FROM PhieuThuePhong where MaKhach= '"+maKhach+"'";
                
        try {
            cn = new MSSQLConnect();
            rs = cn.excuteQuery(sqlString);
            while (rs.next()) {
                trigia = rs.getFloat("TriGia");
            }
        } catch (Exception e) {
            throw new Exception("Không thể lấy trị giá phiếu thuê");

        } finally {
            cn.Close();
        }
        return trigia;
    }
}
